from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from xpinyin import Pinyin
from sql import sqlseverDB
from mini_tools.myexcel import *
import tkinter.messagebox as messagebox
import openpyxl,re,tkinter,copy


class MiniFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Xgn(self).pack(fill=X,padx=3,pady=3)
        TitFra(self).pack(fill=X,padx=3,pady=3)
        QzFgFra(self).pack(fill=X,padx=3,pady=3)
        
def getpy(mes):
    p=Pinyin()
    return p.get_pinyin(mes,splitter='',convert='capitalize')

def getDictDatasByEecel(ws):
    datas=[]    
    headers=[getpy(ws.cell(1,col).value) for col in range(1,ws.max_column+1)]
    for row in range(2,ws.max_row+1):

        currow={}
        for col in range(1,ws.max_column+1):
            curtitle=headers[col-1]
            currow[curtitle]=ws.cell(row,col).value
        datas.append(currow)

    return datas

class Xgn(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        # self.pack(fill=X,padx=3,pady=3)
        self.sheetdatas=[]
        self.statustxt=StringVar()
        self.statustxt.set(f'当前导入表格 {len(self.sheetdatas)} 行')
        self.createWidgets()

    def createWidgets(self):

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=5)
        Label(self, text='生成标题', fg="black", bg="yellow",font=("微软雅黑", 14)).pack(fill=X,padx=5)

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)

        f00=Frame(f0)
        f00.grid(row=1,column=1,padx=5,sticky='nw')
        Label(f00, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择文件',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        f01=Frame(f0)
        f01.grid(row=2,column=1,padx=5,sticky='nw')
        Label(f01, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)
        btnFra1=Frame(f0)
        btnFra1.grid(row=3,column=1,padx=5,sticky='nw')
        Button(btnFra1,text='生成标题',command=self.createTitle).pack(side=LEFT,fill=X,padx=5)

        f02=Frame(f0)
        f02.grid(row=1,column=2,rowspan=3,padx=10,sticky='nw')
        Label(f02, text='生成的标题:').grid(row=1,column=1,sticky='nw')
        self.titTxt=Text(f02,width=60,height=10)
        self.titTxt.grid(row=2,column=1,sticky='nw')

    
    def choosefile(self):

        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt.set(f'当前表格共{max_row}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def createTitle(self):
        
        ws=delempty2(self.fpath)
        datas=getDictDatasByEecel(ws)
        datas.sort(key=lambda x:int(x['SouSuoLiang']),reverse=True)
        self.titTxt.delete(1.0,END)
        tdatas=[]
        zdatas=[]
        for data in datas:
            cwords=data['GuanJianCi'].split()
            clda=[]
            for cw in cwords:
                if cw not in tdatas:
                    tdatas.append(cw)
                    clda.append(cw)
            if clda:
                zdatas.append(clda)

        zdatas=[' '.join(item) for item in zdatas]
        mbtit=''
        for item in zdatas:
            if len(mbtit+item)>255:
                break

            mbtit+=f'{item} '

        mbtit=mbtit[:-2]

        self.titTxt.insert(END,mbtit)

class TitFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        # self.pack(fill=X,padx=3,pady=3)
        self.sheetdatas=[]
        self.statustxt=StringVar()
        self.statustxt.set(f'当前导入表格 {len(self.sheetdatas)} 行')
        self.createWidgets()

    def createWidgets(self):

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=5)
        Label(self, text='提取标题关键词组', fg="black", bg="yellow",font=("微软雅黑", 14)).pack(fill=X,padx=5)

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)

        f00=Frame(f0)
        f00.grid(row=1,column=1,padx=5,sticky='nw')
        Label(f00, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择文件',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        f01=Frame(f0)
        f01.grid(row=2,column=1,padx=5,sticky='nw')
        Label(f01, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)
        btnFra1=Frame(f0)
        btnFra1.grid(row=3,column=1,padx=5,sticky='nw')
        Button(btnFra1,text='提取',command=self.tq).grid(row=1,column=1,sticky='nw',padx=5)
        Button(btnFra1,text='生成过度关键词',command=self.create_guodu).grid(row=1,column=2,sticky='nw',padx=5)
        Button(btnFra1,text='去除无关联行',command=self.filterkw).grid(row=1,column=3,sticky='nw',padx=5)
    
    def choosefile(self):

        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt.set(f'当前表格共{max_row}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def tq(self):
        if not self.DaoRuIpt.get():
            messagebox.showerror('错误','请先导入表格')

        ms = sqlseverDB('{SQL Server}', '199.33.126.84,1433', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')
        ws=delempty2(self.fpath)
        datas=getDictDatasByEecel(ws)
        headers=[ws.cell(1,col).value for col in range(1,ws.max_column+1)]
        dcdatas=[data for data in datas if not data.get('HuoQuChangWeiCi') or data.get('HuoQuChangWeiCi')=='0']
        
        print(f'共 {len(datas)} 个关键词,{len(dcdatas)} 个关键词待查')
        print('正在链接数据库,请等待...')
        rs=ms.ExecQuery('select GuanJianCi,SouSuoLiang from keyword where SouSuoLiang between 300 and 50000')
        print(f'数据库获取到 {len(rs)} 个长尾词')
        allndatas=[]
        ycdata=[data for data in datas if data.get('HuoQuChangWeiCi') and data.get('HuoQuChangWeiCi')!='0']
        for item in ycdata:
            if not item['SouSuoLiang']:
                item['SouSuoLiang']=1
        allndatas.extend(ycdata)
        print(f'已提前将 {len(allndatas)} 个已提取长尾词的保留')
        if len(rs)>0:
            taskcount=0
            for data in dcdatas:
                print(f'任务队列:{len(dcdatas)-taskcount}')
                curndatas=[]
                cskw=data['GuanJianCi']
                cbt=data['BiaoTi']

                glwords=[re.escape(item) for item in cskw.split()]
                for r in rs:
                    flag0=True
                    for gkw in glwords:
                        if not re.search(r'\b{}\b'.format(re.escape(gkw)),r[0],re.I):
                            flag0=False
                            break
                    
                    if flag0:
                        
                        flag1=True
                        glwords2=[re.escape(item) for item in r[0].split()]
                        for gkw in glwords2:
                            if not re.search(r'\b{}\b'.format(re.escape(gkw)),cbt,re.I):
                                flag1=False
                                break
                        if flag1:
                            data0=copy.deepcopy(data)
                            data0['GuanJianCi']=r[0]
                            data0['SouSuoLiang']=r[1]
                            data0['HuoQuChangWeiCi']=1
                            data0['DianJiCiShu']=1
                            curndatas.append(data0)

                if len(curndatas)==0:
                    data00=copy.deepcopy(data)
                    if not data00['SouSuoLiang']:
                        data00['SouSuoLiang']=1
                    allndatas.append(data00)
                    print(f'kw:《{cskw}》,获取到0个长尾词,保留原数据')
                            
                print(f'kw:《{cskw}》,共获取到{len(curndatas)}个长尾词,当前长尾词总数:{len(allndatas)}')
                allndatas.extend(curndatas)
                taskcount+=1
            
            print(f'获取长尾词完毕，共获取{len(allndatas)}个')
            fname=re.search(r'.+/(.+?).xlsx',self.fpath).group(1) 
            npath=self.fpath.replace(fname,'1_查排名_new')
            nwb=openpyxl.Workbook()
            nws=nwb[nwb.sheetnames[0]]
            nws.append(headers)
            allndatas.sort(key=lambda x: (getpy(x['DianPu']) if x['DianPu'] else 1, getpy(x['ChanPin']) if x['ChanPin'] else 1, -int(x['SouSuoLiang'])))
            [nws.append(list(data.values())) for data in allndatas]
            nwb.save(npath)
            messagebox.showinfo('提示',f'成功保存新文件至{npath}')

    def create_guodu(self):

        ws=delempty2(self.fpath)
        datas=getDictDatasByEecel(ws)
        wb=openpyxl.load_workbook(self.fpath)
        worksheet=wb[wb.sheetnames[0]]
        row_number_to_change=2
        ind_GuoDuGuanJianCi=list(datas[0].keys()).index('GuoDuGuanJianCi')+1
        for data in datas:
            if not data['GuoDuGuanJianCi']:

                tqkws=[item for item in datas if item['ChanPinLianJie']==data['ChanPinLianJie']]
                data['GuoDuGuanJianCi']=self.createtit(tqkws)
                worksheet.cell(row_number_to_change,ind_GuoDuGuanJianCi).value=self.createtit(tqkws)
                print(f'kw:《{data["GuanJianCi"]}》,成功获取过度关键词 {data["GuoDuGuanJianCi"]}')

            row_number_to_change+=1
        wb.save(self.fpath)
        print('生成过度关键词完毕')
        messagebox.showinfo('提示',"生成过度关键词完毕")
    
    def createtit(self,datas):

        datas.sort(key=lambda x:int(x['SouSuoLiang']),reverse=True)
        tdatas=[]
        zdatas=[]
        for data in datas:
            cwords=data['GuanJianCi'].split()
            clda=[]
            for cw in cwords:
                if cw not in tdatas:
                    tdatas.append(cw)
                    clda.append(cw)
            if clda:
                zdatas.append(clda)

        zdatas=[' '.join(item) for item in zdatas]
        mbtit=''
        for item in zdatas:
            if len(mbtit+item)>255:
                break

            mbtit+=f'{item} '

        return mbtit[:-1]


    def filterkw(self):

        fpath=self.DaoRuIpt.get().strip()
        if fpath.endswith('.xlsx'):
            delempty(fpath)
            wb=openpyxl.load_workbook(fpath)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            max_col=ws.max_column
            kw_index=None
            tit_index=None
            header=[ws.cell(1,j).value for j in range(1,max_col+1)]
            curcol=1
            for v in header:
                if v=='关键词':
                    kw_index=curcol
                if v=='标题':
                    tit_index=curcol
                curcol+=1

            if not kw_index or not tit_index:
                print(f'未找到关键词列')
            print(f'找到关键词列,第{kw_index}列,标题列为第{tit_index}列')
            npath=fpath.replace('.xlsx','_new.xlsx')
            nwb=openpyxl.Workbook()
            nws=nwb[nwb.sheetnames[0]]
            nws.append(header)
            for j in range(2, max_row + 1):
                cur_kw=str(ws.cell(row=j, column=kw_index).value).strip()
                cur_tit=str(ws.cell(row=j, column=tit_index).value).strip()
                flag=True
                for ckw in cur_kw.split():
                    if ckw.lower() not in cur_tit.lower():
                        flag=False
                        break
                if flag:
                    cdata=[]
                    for col in range(1,max_col+1):
                        cdata.append(ws.cell(j,col).value)
                    nws.append(cdata)
            
            print(f'过滤前{max_row}行,过滤后{nws.max_row}行')
            nwb.save(npath)
            print(f'新表已保存至{npath}')

class QzFgFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        # self.pack(fill=X,padx=3,pady=3)
        self.statustxt=StringVar()
        self.statustxt.set(f'未导入表格')
        self.statustxt2=StringVar()
        self.statustxt2.set(f'未导入表格')
        self.createWidgets()
    def createWidgets(self):

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=5)
        Label(self, text='EXCEL处理', fg="black", bg="yellow",font=("微软雅黑", 14)).pack(fill=X,padx=5)

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)

        f00=Frame(f0)
        f00.grid(row=1,column=1,sticky='nw',padx=5)
        ttk.Separator(f0,orient='vertical').grid(row=1,column=2,sticky='nw',padx=50)
        f01=Frame(f0)
        f01.grid(row=1,column=3,sticky='nw',ipadx=30)
        
        Label(f00, text='文档处理', fg="black", bg="#3299CC",font=("微软雅黑", 12)).pack(fill=X,padx=5,pady=2)

        f000=Frame(f00)
        f000.pack(pady=5,fill=X,padx=5)
        Label(f000, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f000)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f000,text='选择文件',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        f002=Frame(f00)
        f002.pack(fill=X,padx=5)
        Label(f002, text='导入目录:').pack(side=LEFT, padx=2)
        self.DrmrIpt = Entry(f002)
        self.DrmrIpt.pack(side=LEFT, padx=2)
        Button(f002,text='选择目录',command=self.chooseDir).pack(side=LEFT,padx=2)

        f001=Frame(f00)
        f001.pack(pady=5,fill=X,padx=5)
        Label(f001, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)
        btnFra1=Frame(f00)
        btnFra1.pack(pady=5,fill=X,padx=5)
        Button(btnFra1,text='去重',command=self.qzec).pack(side=LEFT,fill=X,padx=5)
        Button(btnFra1,text='统一数字类型',command=self.tongyi).pack(side=LEFT,fill=X,padx=5)


        Label(f01, text='分割excel', fg="black", bg="#3299CC",font=("微软雅黑", 12)).pack(fill=X,padx=5,pady=2)
        f010=Frame(f01)
        f010.pack(pady=5,fill=X,padx=5)
        f014=Frame(f01)
        f014.pack(fill=X,padx=5)
        f015=Frame(f01)
        f015.pack(fill=X,padx=5)
        f011=Frame(f01)
        f011.pack(pady=5,fill=X,padx=5)
        f012=Frame(f01)
        f012.pack(pady=5,fill=X,padx=5)
        f013=Frame(f01)
        f013.pack(pady=5,fill=X,padx=5)

        Label(f010, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt2 = Entry(f010)
        self.DaoRuIpt2.pack(side=LEFT, padx=2, pady=2)
        Button(f010,text='选择文件',command=self.choosefile2).pack(side=LEFT,padx=2, pady=2)

        Label(f014, text='导入目录:').pack(side=LEFT, padx=2)
        self.DrmrIpt2 = Entry(f014)
        self.DrmrIpt2.pack(side=LEFT, padx=2)
        Button(f014,text='选择目录',command=self.chooseDir2).pack(side=LEFT,padx=2)

        Label(f015, textvariable=self.statustxt2,fg='red').pack(side=LEFT, padx=2)

        Label(f012, text='分割行数:').pack(side=LEFT, padx=2, pady=2)
        self.searchKwIpt = Entry(f012)
        self.searchKwIpt.insert(0,500)
        self.searchKwIpt.pack(side=LEFT, padx=2, pady=2)

        Button(f013,text='分割',command=self.fgec,fg='#ffffff',bg='green',width=20).grid(row=1,column=1,sticky='nw',padx=10,pady=2)
        Button(f013,text='合并',command=self.hb,fg='#ffffff',bg='green',width=20).grid(row=1,column=2,sticky='nw',padx=10,pady=2)

    
    def choosefile(self):

        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt.set(f'当前表格共{max_row}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def choosefile2(self):

        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt2.delete(0, END)
            self.DaoRuIpt2.insert(0,select_path)
            self.fpath2=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt2.set(f'当前表格共{max_row}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')

    def chooseDir(self):
        import os
        select_path=filedialog.askdirectory()
        self.DrmrIpt.delete(0, END)
        self.DrmrIpt.insert(0,select_path)
        fs=os.listdir(select_path)
        ecls=[select_path+r'/'+f for f in fs if f.endswith('.xlsx')]
        if len(ecls)==0:
            messagebox.showinfo('提示','当前目录下无excel文件')
            self.DrmrIpt.delete(0, END)
            return
        self.ecls=ecls
    
    def chooseDir2(self):
        import os
        select_path=filedialog.askdirectory()
        self.DrmrIpt2.delete(0, END)
        self.DrmrIpt2.insert(0,select_path)
        self.dirpath2=select_path
        fs=os.listdir(select_path)
        ecls=[select_path+r'/'+f for f in fs if f.endswith('.xlsx')]
        if len(ecls)==0:
            messagebox.showinfo('提示','当前目录下无excel文件')
            self.DrmrIpt2.delete(0, END)
            return
        self.ecls2=ecls

    def qzec(self):
        if not self.DaoRuIpt.get():
            return
        delduplicates(self.fpath)
        print('去重完毕')
        messagebox.showinfo('提示','操作完毕')
    
    def hb(self):
        if not self.DrmrIpt2.get():
            return
        print(self.ecls2,self.dirpath2)
        hebing(self.ecls2,self.dirpath2)
        print('合并完毕')
        messagebox.showinfo('提示','操作完毕')

    def tongyi(self):

        if not self.DrmrIpt.get().strip():
            return
        Shuzitongyi(self.ecls)
        messagebox.showinfo('提示','操作完毕')
        
    def fgec(self):
        if not self.DaoRuIpt2.get():
            return
        qiepian(self.fpath2,int(self.searchKwIpt.get()))
        print('分割完毕')
        messagebox.showinfo('提示','操作完毕')


# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('表格上传下载')

# QzFgFra(list_win).pack(fill=X,padx=3,pady=3)

# list_win.mainloop()

